package com.hdl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.hdl.dto.OrderSearch;
import com.hdl.entity.MallOrder;
import com.hdl.entity.MallOrderDetail;
import com.hdl.entity.MallProduct;
import com.hdl.util.JdbcUtil;

public class OrderDAO {

	/**
	 * - 根据传入的search查询条件search.getKey()查询订单总数总数
	 * @param search
	 * @return
	 * @throws SQLException
	 */
	public long countBySearch(OrderSearch search)throws SQLException{
		String sql = "SELECT count(*) as c FROM mall_order INNER JOIN mall_order_detail ON mall_order.MALL_ORDER_ID = mall_order_detail.MALL_ORDER_ID";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtil.getConnection();
			if (search.getKey() != null && !search.equals("")) {
				sql += " AND mall_order.MALL_ORDER_NAME like concat('%',?,'%')";
				pstmt = conn.prepareStatement(sql);
				pstmt.setObject(1, search.getKey());
			} else {
				pstmt = conn.prepareStatement(sql);
			}
			rs = pstmt.executeQuery();
			rs.next();
			return rs.getLong(1);
		} finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
	}
	
	/**
	 * -根据传入search查询条件中的search.getpageSize()每页最大数和search.getpageNumber()页码数还有search.getKey()模糊查询来分页查询
	 * 
	 * @param search
	 * @return
	 * @throws SQLException
	 */
	public List<MallOrder> selectSearch(OrderSearch search, int index, int len) throws SQLException {
		String sql = "SELECT mall_order.*,mall_order_detail.* FROM mall_order INNER JOIN mall_order_detail ON mall_order.MALL_ORDER_ID = mall_order_detail.MALL_ORDER_ID ";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		try {
			conn = JdbcUtil.getConnection();
			if (search.getKey() != null && !search.equals("")) {
				sql += " where mall_order.MALL_ORDER_NAME like concat('%',?,'%') order by mall_order.MALL_ORDER_ID limit ?,?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setObject(1, search.getKey());
				pstmt.setObject(2, index);
				pstmt.setObject(3, len);
			} else {
				sql += " order by mall_order.MALL_ORDER_ID limit ?,?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setObject(1, index);
				pstmt.setObject(2, len);
			}
			
			rs = pstmt.executeQuery();
			List<MallOrder> list = new ArrayList<MallOrder>();
			while (rs.next()) {
				MallOrder entity = new MallOrder();
				entity.setMallOrderId(rs.getString("MALL_ORDER_ID"));
				entity.setMallOrderName(rs.getString("MALL_ORDER_NAME"));
				entity.setMallOrderDate(rs.getTimestamp("MALL_ORDER_DATE"));
				entity.setMallOrderNum(rs.getString("MALL_ORDER_NUM"));
				entity.setMallOrderAddress(rs.getString("MALL_ORDER_ADDRESS"));
				entity.setMallOrderCash(rs.getInt("MALL_ORDER_CASH"));
				entity.setMallOrderStatus(rs.getInt("MALL_ORDER_STATUS"));
				
				//查找该订单的订单详情列表
				sql = "select * from mall_order_detail where MALL_ORDER_ID=?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setObject(1, entity.getMallOrderId());
				rs1 = pstmt.executeQuery();
				while(rs1.next()) {
					MallOrderDetail mallOrderDetail = new MallOrderDetail();
					mallOrderDetail.setMallOrderDetailCount(rs1.getInt("MALL_ORDER_DETAIL_COUNT"));
					mallOrderDetail.setMallOrderDetailPrice(rs1.getInt("MALL_ORDER_DETAIL_PRICE"));
					mallOrderDetail.setMallOrderId(rs1.getString("MALL_ORDER_ID"));
					
					//查找该订单详情中商品
					sql = "select * from mall_product where MALL_PRODUCT_ID=?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setObject(1, rs1.getString("MALL_PRODUCT_ID"));
					rs2 = pstmt.executeQuery();
					rs2.next();
					MallProduct mallProduct = new MallProduct();
					mallProduct.setMallProductId(rs2.getString("MALL_PRODUCT_ID"));
					mallProduct.setMallProductName(rs2.getString("MALL_PRODUCT_NAME"));
					mallProduct.setMallProductPath(rs2.getString("MALL_PRODUCT_PATH"));
					mallProduct.setMallProductPrice1(rs2.getInt("MALL_PRODUCT_PRICE_1"));
					
					mallOrderDetail.setProduct(mallProduct);
					entity.list.add(mallOrderDetail);
				}
				list.add(entity);
			}
			return list;
		} finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
	}
	
	/**
	 * - 根据传入的订单主键和要修改的状态来修改订单的状态
	 * - 0：未支付     1：已支付，待发货     2：已发货    3：已签收
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public int updataStatus(String id,int status)throws SQLException{
		if(status==3) {
			String sql = "update mall_order set MALL_ORDER_STATUS=?,MALL_ORDER_SENDTIME=? where MALL_ORDER_ID=? ";
			try(Connection conn = JdbcUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql);){
				JdbcUtil.setParams(pstmt, status,new Date(),id);
				return pstmt.executeUpdate();
			}
		}else {
			String sql = "update mall_order set MALL_ORDER_STATUS=? where MALL_ORDER_ID=? ";
			try(Connection conn = JdbcUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql);){
				JdbcUtil.setParams(pstmt, status,id);
				return pstmt.executeUpdate();
			}
		}
	}
	
}
